/**
 * 
 */
package rentReturnService;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;

import constants.Constants;
import rentReturnEntity.RentEntity;


/**
 * @author Aakash Singh
 *
 */
public class RentService {

	private Connection dbConnection;
	private PreparedStatement sqlStatement;

	public RentService(){
		try {
			Class.forName(Constants.JDBC_DRIVER);
			dbConnection = DriverManager.getConnection(Constants.DB_URL,
					Constants.USER_NAME, Constants.PWD);
			System.out.println("DB Connection established");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	
	
	
	
	public ResultSet fetchOverdueVehicle() throws SQLException
	{
		ResultSet resultset = null;
		String query = "select * from vehicle v join rental_vehicle rv on rv.vehicleId= v.vehicleId where status='rented' and nextAvailableDate is not null and rentID is not null order by v.vehicleClass";
		try {
			sqlStatement = dbConnection.prepareStatement(query);
			resultset = sqlStatement.executeQuery();
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			throw sqlException;
		}
		return resultset;
	}
	/**
	 * This method selects a list of available vehicles for the given vehicleType
	 * @param vehicleType
	 * @throws
	 * @return
	 */
	public ResultSet fetchVehicle(String vehicleType) throws SQLException{
	
		ResultSet resultset = null;
		String query = "select vehicle.vehicleId, vehicle.VehicleType,vehicle.VehicleClass,vehicle.VehicleName from vehicle , rental_vehicle where vehicle.vehicleType = ? "
				+ "and vehicle.vehicleId = rental_vehicle.vehicleId and rental_vehicle.status = \"available\" ";
		try {
			sqlStatement = dbConnection.prepareStatement(query);
			sqlStatement.setString(1, vehicleType);
			resultset = sqlStatement.executeQuery();
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			throw sqlException;
		}
		return resultset;

	}

	/**
	 * This method saves the rent details in the rent table 
	 * @param rent
	 * @throws SQLException
	 * @return
	 */
	public ResultSet saveRent(RentEntity rent,LocalDate returnDate) throws SQLException{
		ResultSet rs = null;
		String query = "INSERT INTO rent VALUES (null,?,?,?,?,?,?,?,?,?);";
		String query1="UPDATE rental_vehicle SET status='rented',rentId=?,nextAvailableDate=? where vehicleid=?";
		String query3 = "UPDATE reservation SET status='rented' where reservationID=?";

		String query2 = "SELECT last_insert_id() as last_id FROM rent";
		try {
			dbConnection.setAutoCommit(false);
			sqlStatement = dbConnection.prepareStatement(query);
			sqlStatement.setInt(1, rent.getReserveId());
			sqlStatement.setInt(2,rent.getVehicleId());
			sqlStatement.setString(3, rent.getRentDate().toString());
			sqlStatement.setString(4, rent.getRentTime());
			sqlStatement.setInt(5, rent.getOdometerReading());
			sqlStatement.setInt(6, rent.getAgeCheck());
			sqlStatement.setInt(7, rent.getCreditCheck());
			sqlStatement.setLong(8, rent.getRentAgreementNumber());
			sqlStatement.setString(9, rent.getVehicleCondition());
	
			
			
			sqlStatement.executeUpdate();
			dbConnection.commit();
			sqlStatement=dbConnection.prepareStatement(query3);
			sqlStatement.setInt(1, rent.getReserveId());
			sqlStatement.executeUpdate();
			dbConnection.commit();
			
			
			sqlStatement = dbConnection.prepareStatement(query2);
			rs = sqlStatement.executeQuery();
			if(rs==null)
				System.out.println("RS is null");
			else
				System.out.println("RS is not null");
			if(rs.next())
			System.out.println(rs.getInt("last_id"));
			
			dbConnection.setAutoCommit(false);
			sqlStatement=dbConnection.prepareStatement(query1);
			sqlStatement.setInt(1, rs.getInt("last_id"));
			sqlStatement.setInt(3, rent.getVehicleId());
			sqlStatement.setString(2, returnDate.toString());
			sqlStatement.executeUpdate();
			dbConnection.commit();
		} 
		catch (SQLException e1) {
			if(dbConnection != null){
				System.out.println("transaction is rolled back");
				try {
					dbConnection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		finally{
			try {
				dbConnection.setAutoCommit(true);
				
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
		
		return rs;
	}

	public void closeConnection() {

		try {
			if (sqlStatement != null) {
				sqlStatement.close();
			}
			if (dbConnection != null) {
				dbConnection.close();
			}
		} catch (SQLException sqlExec) {
			sqlExec.printStackTrace();
		}

	}




	/**
	 * @param phoneNumber
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet fetchUserName(String phoneNumber) throws SQLException {
		ResultSet resultset = null;
		String query = "select * from user where phoneNumber=?";
		try {
			sqlStatement = dbConnection.prepareStatement(query);
			System.out.println("fetch usre name phonenUmber " + phoneNumber);
			sqlStatement.setString(1, phoneNumber);
			resultset = sqlStatement.executeQuery();
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			throw sqlException;
		}
		return resultset;
	}
}